﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using VNPT;
using System.IO;
using VNPT.Data;

namespace VNPT.DoiSoat
{
    public partial class uNoiFileGphone : VNPT.Core.uBase
    {
        public uNoiFileGphone()
        {
            InitializeComponent();

            bgWork.RunWorkerCompleted += (a, e) =>
            {
                SauKhiChay();
                if (e.Error == null)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("Đã xử lý xong, vui lòng kiểm tra lại!", "Hoàn thành!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show(e.Error.Message, "Hoàn thành!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }


                labelControl4.Text = "Đã hoàn thành...";
                progressBarControl1.EditValue = progressBarControl1.Properties.Maximum;
            };
            bgWork.ProgressChanged += (a, e) =>
            {
                progressBarControl1.EditValue = e.ProgressPercentage;
                labelControl4.Text = string.Format("Đang nối file {0}...", te);
                AppendTextBox(DateTime.Now.ToString("HH:mm:ss") + " " + labelControl4.Text + Environment.NewLine);
            };
            bgWork.DoWork += (a, e) =>
            {
                #region Thực hiện
                te = "Nối file cước GPhone trong nước!";
                bgWork.ReportProgress(0);
                if (Directory.Exists((btnQuocTe.EditValue ?? "").ToString()))
                {
                    ThucHien_Gphone_QuocTe(btnQuocTe.Text);
                }
                te = "Nối file cước GPhone Quốc tế!";
                bgWork.ReportProgress(0);
                if (Directory.Exists((btnTrongNuoc.EditValue ?? "").ToString()))
                {
                    ThucHien_Gphone_TrongNuoc(btnTrongNuoc.Text);
                }
                te = "Nối file cước SMS!";
                bgWork.ReportProgress(0);
                if (Directory.Exists((btnSMS.EditValue ?? "").ToString()))
                {
                    ThucHien_SMS(btnSMS.Text);
                }
                #endregion
                //TRong nước đến
                ThucHien_Chung(btnTrongNuoc_Den.Text, "", "", "");
                //Trong nươc đi
                ThucHien_Chung(btnTrongNuoc.Text, "CT_GPHONE_{0}_{1:D2}_DLK", @"create table {0}
                (CHUGOI    CHARACTER(12) Not NULL  ,
                BIGOI     CHARACTER(25) Not NULL ,
                NGAYBD    CHARACTER(6) Not NULL,
                 GIOBD     CHARACTER(6) Not NULL,
                 TGGOI     NUMERIC(6) Not NULL,
                 PHUT      NUMERIC(10) Not NULL,
                 REC_TYPE     CHARACTER(3) Not NULL,
                 LAC      CHARACTER(5) Not NULL,
                 CELLID      CHARACTER(5) Not NULL,
                 THIRDPARTY      CHARACTER(15) Not NULL,
                 SS_CODE CHARACTER(5) Not NULL,
                 TR_THAI CHARACTER(10) Not NULL)", @"INSERT INTO {0} (CHUGOI,BIGOI,NGAYBD, GIOBD, TGGOI, PHUT, REC_TYPE, LAC, CELLID, THIRDPARTY, SS_CODE, TR_THAI)
                                                    SELECT A_SUBS AS CHUGOI, B_SUBS AS BIGOI,DATE AS NGAYBD, TIME AS GIOBD, DURATION AS TGGOI, CEILING(DURATION /60) AS PHUT, REC_TYPE, LAC, CELLID, THIRdPARTY, SS_CODE, TR_THAI FROM {1}");
                //Quốc tế
                ThucHien_Chung(btnQuocTe.Text, "", "", "");
                //SMS
                ThucHien_Chung(btnSMS.Text, "", "", "");
            };

        }
        string te = "";

        private void ThucHien_Gphone_QuocTe(string Path)
        {
            string FileName = string.Format("CT_GPHONE_QTE_{0}_{1:D2}_DLK", DoiSoatSetting.NAM, DoiSoatSetting.THANG);
            string SourceStr = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            DbNetData SourceConnection = new DbNetData(SourceStr, DataProvider.OleDb);

            DbNetData Db = new DbNetData(SourceStr, DataProvider.OleDb);
            Db.Open();
            var sqlcreate = string.Format(@"create table {0}
                (CHUGOI    CHARACTER(22) Not NULL,
                 BIGOI     CHARACTER(22) Not NULL,
                 NGAYBD    DATE Not NULL,
                 GIOBD     CHARACTER(8) Not NULL,
                 TGGOI     NUMERIC(6) Not NULL,
                 PHUT      NUMERIC(10) Not NULL,
                 ZONE      CHARACTER(25) Not NULL,
                 NATIONAL  NUMERIC(11) Not NULL,
                 INTL      NUMERIC(12) Not NULL,
                 INTL_VND  NUMERIC(11) Not NULL,
                 GHICHU   CHARACTER(20) Not NULL,
                 TR_THAI   CHARACTER(10) Not NULL)", FileName);

            Db.ExecuteNonQuery(new CommandConfig(sqlcreate));

            //SELECT A_SUBS AS CHUGOI,B_SUBS AS BIGOI, DATE AS NGAYBD, TIME AS GIOBD,DURATION AS TGLL, ZONE, NATIONAL,INTL,INTL_VND, COMMENT, TR_THAI FROM DBF()

            var files = Directory.GetFiles(Path).Where(p => p.ToUpper().EndsWith(".DBF"));


            // File.Copy(files.First(), string.Format("{0}\\{1}.DBF", DoiSoatSetting.Folder_LUU, FileName).Replace(@"\\",@"\"), true);

            int i = 0;
            foreach (var item in files)
            {
                i++;
                if (bgWork.CancellationPending)
                {
                    continue;
                }
                te = item;
                var sql = string.Format(@"INSERT INTO {0} (CHUGOI ,BIGOI,NGAYBD, GIOBD, TGGOI, PHUT, ZONE, NATIONAL, INTL, INTL_VND, GHICHU, TR_THAI)
                                                    SELECT A_SUBS AS CHUGOI ,B_SUBS AS BIGOI,DATE AS NGAYBD, TIME AS GIOBD, DURATION AS TGGOI, CEILING(DURATION/60) AS PHUT, ZONE, NATIONAL, INTL, INTL_VND, COMMENT AS GHICHU, TR_THAI FROM {1}", FileName, item);

                QueryCommandConfig Query2 = new QueryCommandConfig(sql);
                Db.ExecuteNonQuery(Query2);
                bgWork.ReportProgress(i * 100 / files.Count());
            }

            Db.Close();
        }
       
        private void ThucHien_Chung(string Path, string TableName, string SQLCreate, string SQLInsert)
        {
            if (!Directory.Exists(Path))
            {
                return;
            }

            string FileName = string.Format(TableName, DoiSoatSetting.NAM, DoiSoatSetting.THANG);
            string SourceStr = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            DbNetData SourceConnection = new DbNetData(SourceStr, DataProvider.OleDb);

            DbNetData Db = new DbNetData(SourceStr, DataProvider.OleDb);
            Db.Open();

            var sqlcreate = string.Format(SQLCreate, FileName);
            Db.ExecuteNonQuery(new CommandConfig(sqlcreate));
            var files = Directory.GetFiles(Path).Where(p => p.ToUpper().EndsWith(".DBF"));

            int i = 0;
            foreach (var item in files)
            {
                i++;
                if (bgWork.CancellationPending)
                {
                    continue;
                }
                te = item;
                var sql = string.Format(SQLInsert, FileName, item);

                QueryCommandConfig Query2 = new QueryCommandConfig(sql);
                Db.ExecuteNonQuery(Query2);
                bgWork.ReportProgress(i * 100 / files.Count());
            }

            Db.Close();
        }

        private void ThucHien_Gphone_GoiDen(string Path)
        {
            string FileName = string.Format("CT_GPHONE_DEN_{0}_{1:D2}_DLK", DoiSoatSetting.NAM, DoiSoatSetting.THANG);
            string SourceStr = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            DbNetData SourceConnection = new DbNetData(SourceStr, DataProvider.OleDb);

            DbNetData Db = new DbNetData(SourceStr, DataProvider.OleDb);
            Db.Open();
            var sqlcreate = string.Format(@"create table {0}
                (CHUGOI    CHARACTER(22) Not NULL,
                 BIGOI     CHARACTER(22) Not NULL,
                 NGAYBD    DATE Not NULL,
                 GIOBD     CHARACTER(8) Not NULL,
                 TGGOI     NUMERIC(6) Not NULL,
                 PHUT      NUMERIC(10) Not NULL,
                 ZONE      CHARACTER(25) Not NULL,
                 NATIONAL  NUMERIC(11) Not NULL,
                 INTL      NUMERIC(12) Not NULL,
                 INTL_VND  NUMERIC(11) Not NULL,
                 GHICHU   CHARACTER(20) Not NULL,
                 TR_THAI   CHARACTER(10) Not NULL)", FileName);

            Db.ExecuteNonQuery(new CommandConfig(sqlcreate));

            //SELECT A_SUBS AS CHUGOI,B_SUBS AS BIGOI, DATE AS NGAYBD, TIME AS GIOBD,DURATION AS TGLL, ZONE, NATIONAL,INTL,INTL_VND, COMMENT, TR_THAI FROM DBF()

            var files = Directory.GetFiles(Path).Where(p => p.ToUpper().EndsWith(".DBF"));

            int i = 0;
            foreach (var item in files)
            {
                i++;
                if (bgWork.CancellationPending)
                {
                    continue;
                }
                te = item;
                var sql = string.Format(@"INSERT INTO {0} (CHUGOI ,BIGOI,NGAYBD, GIOBD, TGGOI, PHUT, ZONE, NATIONAL, INTL, INTL_VND, GHICHU, TR_THAI)
                                                    SELECT A_SUBS AS CHUGOI ,B_SUBS AS BIGOI,DATE AS NGAYBD, TIME AS GIOBD, DURATION AS TGGOI, CEILING(DURATION/60) AS PHUT, REC_TYPE, MSKEY,LAC,CELLID, THIRDPATRY, SS_CODE FROM {1}", FileName, item);

                QueryCommandConfig Query2 = new QueryCommandConfig(sql);
                Db.ExecuteNonQuery(Query2);
                bgWork.ReportProgress(i * 100 / files.Count());
            }

            Db.Close();
        }


        private void ThucHien_Gphone_TrongNuoc(string Path)
        {
            string FileName = string.Format("CT_GPHONE_{0}_{1:D2}_DLK", DoiSoatSetting.NAM, DoiSoatSetting.THANG);
            string SourceStr = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            DbNetData SourceConnection = new DbNetData(SourceStr, DataProvider.OleDb);

            DbNetData Db = new DbNetData(SourceStr, DataProvider.OleDb);
            Db.Open();
            
            var sqlcreate = string.Format(@"create table {0}
                (CHUGOI    CHARACTER(12) Not NULL  ,
                BIGOI     CHARACTER(25) Not NULL ,
                NGAYBD    CHARACTER(6) Not NULL,
                 GIOBD     CHARACTER(6) Not NULL,
                 TGGOI     NUMERIC(6) Not NULL,
                 PHUT      NUMERIC(10) Not NULL,
                 REC_TYPE     CHARACTER(3) Not NULL,
                 LAC      CHARACTER(5) Not NULL,
                 CELLID      CHARACTER(5) Not NULL,
                 THIRDPARTY      CHARACTER(15) Not NULL,
                 SS_CODE CHARACTER(5) Not NULL,
                 TR_THAI CHARACTER(10) Not NULL)", FileName);

            Db.ExecuteNonQuery(new CommandConfig(sqlcreate));

            //SELECT A_SUBS AS CHUGOI,B_SUBS AS BIGOI, DATE AS NGAYBD, TIME AS GIOBD,DURATION AS TGLL, ZONE, NATIONAL,INTL,INTL_VND, COMMENT, TR_THAI FROM DBF()

            var files = Directory.GetFiles(Path).Where(p => p.ToUpper().EndsWith(".DBF"));


            // File.Copy(files.First(), string.Format("{0}\\{1}.DBF", DoiSoatSetting.Folder_LUU, FileName).Replace(@"\\",@"\"), true);

            int i = 0;
            foreach (var item in files)
            {
                i++;
                if (bgWork.CancellationPending)
                {
                    continue;
                }
                te = item;
                var sql = string.Format(@"INSERT INTO {0} (CHUGOI,BIGOI,NGAYBD, GIOBD, TGGOI, PHUT, REC_TYPE, LAC, CELLID, THIRDPARTY, SS_CODE, TR_THAI)
                                                    SELECT A_SUBS AS CHUGOI, B_SUBS AS BIGOI,DATE AS NGAYBD, TIME AS GIOBD, DURATION AS TGGOI, CEILING(DURATION /60) AS PHUT, REC_TYPE, LAC, CELLID, THIRdPARTY, SS_CODE, TR_THAI FROM {1}", FileName, item);

                QueryCommandConfig Query2 = new QueryCommandConfig(sql);
                Db.ExecuteNonQuery(Query2);
                bgWork.ReportProgress(i * 100 / files.Count());
            }

            Db.Close();
        }
      
        private void ThucHien_SMS(string Path)
        {
            string FileName = string.Format("CT_SMS_{0}_{1:D2}_DLK", DoiSoatSetting.NAM, DoiSoatSetting.THANG);
            string SourceStr = string.Format(@"Provider=vfpoledb;Data Source={0};", DoiSoatSetting.Folder_LUU);
            DbNetData SourceConnection = new DbNetData(SourceStr, DataProvider.OleDb);

            DbNetData Db = new DbNetData(SourceStr, DataProvider.OleDb);
            Db.Open();
            var sqlcreate = string.Format(@"create table {0}
                                            (CHUGOI     CHARACTER(12) Not NULL ,
                                             BIGOI    CHARACTER(25) Not NULL  ,
                                             NGAYBD    CHARACTER(6) Not NULL,
                                             GIOBD     CHARACTER(8) Not NULL,
                                             SMS_TYPE    CHARACTER(1) Not NULL ,
                                             STATUS CHARACTER(1) Not NULL,
                                             TR_THAI CHARACTER(10) Not NULL)", FileName);

            Db.ExecuteNonQuery(new CommandConfig(sqlcreate));

            //SELECT A_SUBS AS CHUGOI,B_SUBS AS BIGOI, DATE AS NGAYBD, TIME AS GIOBD,DURATION AS TGLL, ZONE, NATIONAL,INTL,INTL_VND, COMMENT, TR_THAI FROM DBF()

            var files = Directory.GetFiles(Path).Where(p => p.ToUpper().EndsWith(".DBF"));


            // File.Copy(files.First(), string.Format("{0}\\{1}.DBF", DoiSoatSetting.Folder_LUU, FileName).Replace(@"\\",@"\"), true);

            int i = 0;
            foreach (var item in files)
            {
                i++;
                if (bgWork.CancellationPending)
                {
                    continue;
                }
                te = item;
                var sql = string.Format(@"INSERT INTO {0} (CHUGOI, BIGOI, NGAYBD, GIOBD,SMS_TYPE ,STATUS,TR_THAI)
                                                    SELECT A_SUBS AS CHUGOI ,B_SUBS AS BIGOI,DATE AS NGAYBD, TIME AS GIOBD,SMS_TYPE ,STATUS,TR_THAI FROM {1}", FileName, item);

                QueryCommandConfig Query2 = new QueryCommandConfig(sql);
                Db.ExecuteNonQuery(Query2);
                bgWork.ReportProgress(i * 100 / files.Count());
            }

            Db.Close();
        }
      
        private bool KT_HopLe()
        {
            if (this.btnQuocTe.EditValue == null && this.btnTrongNuoc.EditValue == null && this.btnSMS.EditValue == null)
            {
                string text = "Chọn ít nhất một đường dẫn thư mục cước gốc!";
                string caption = "Thông báo!";
                DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            return true;
        }

        private void TruocKhiChay()
        {
            DevExpress.XtraEditors.XtraForm f;
            if (this.Parent == null) return;
            if (this.Parent.Parent == null) return;
            else
            {
                var t2 = this.Parent.Parent as DevExpress.XtraEditors.XtraForm;
                f = t2;
            }

            var t = this.Parent.Parent.Parent as DevExpress.XtraEditors.XtraForm;
            if (t != null)
            {
                f = t;
            }
            if (f != null)
            {
                f.FormClosing += (a1, e1) =>
                {
                    if (bgWork.IsBusy)
                    {
                        string text = "Chương trình đang chạy, không thể đóng form! Vui lòng hủy trước khi đóng chương trình?";
                        string caption = "Xác nhận?";
                        DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        e1.Cancel = true;
                    }
                };

            }

            var m = this.Parent as DevExpress.XtraWizard.WizardPage;
            if (m != null)
            {
                m.AllowNext = false;
                m.AllowBack = false;
            }
        }

        private void SauKhiChay()
        {
            var m = this.Parent as DevExpress.XtraWizard.WizardPage;
            if (m != null)
            {
                m.AllowNext = true;
                m.AllowBack = true;
            }
        }

        public override void SaveSetting()
        {

            base.SaveSetting();
        }
        public override void LoadSetting()
        {
            btnSavePath.Text = DoiSoatSetting.Folder_LUU;
            base.LoadSetting();
        }
        public void AppendTextBox(string value)
        {
            if (InvokeRequired)
            {
                this.Invoke(new Action<string>(AppendTextBox), new object[] { value });
                return;
            }
            memoEdit1.Text = value + memoEdit1.Text;
        }
        private void btnDung_Click(object sender, EventArgs e)
        {
            string text = "Bạn chắc có muốn dừng thực hiện không?";
            string caption = "Hủy bỏ?";
            if (DevExpress.XtraEditors.XtraMessageBox.Show(text, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                this.bgWork.CancelAsync();
            }
        }

        private void btnThucHien_Click(object sender, EventArgs e)
        {
            TruocKhiChay();
            if (KT_HopLe())
            {
                bgWork.RunWorkerAsync();
            }
        }
    }
}
